import {TableBuilder, createExecutor, TableBuildResult, SqlBuilder} from '../src/core/sqlbuilder';
import Database from 'better-sqlite3';

// 创建内存数据库
const db = new Database(':memory:');
const executor = createExecutor(db, { debug: true });

// 测试创建表
function testCreateTable() {
  console.log('=== 测试创建表 ===');

  // 默认时间戳
  const usersTable = new TableBuilder('users')
    .ifNotExist()
    .id()
    .text('name', { notNull: true })
    .text('email', { unique: true })
    .integer('age')
    .timestamps()
    .softDeletes()
    .unique('email');

  const result = usersTable.build();
  const usersSql = result.sql;
  const indexSqls = result.indexSqls;

  // 打印SQL
  console.log('表SQL:', usersSql);
  console.log('索引SQL:', indexSqls);

  // 执行SQL
  executor.execute(usersSql);
  for (const indexSql of indexSqls) {
    executor.execute(indexSql);
  }

  // 毫秒级时间戳
  const postsTable = new TableBuilder('posts')
    .ifNotExist()
    .id()
    .text('title', { notNull: true })
    .text('content')
    .integer('user_id', {
      notNull: true,
      references: {
        table: 'users',
        column: 'id',
        onDelete: 'CASCADE'
      }
    })
    .timestamps({ useMilliseconds: true })
    .softDeletes({ useMilliseconds: true })
    .index('user_id');

  const postsResult = postsTable.build();
  const postsSql = postsResult.sql;
  const postsIndexSqls = postsResult.indexSqls;

  // 打印SQL
  console.log('\n表SQL:', postsSql);
  console.log('索引SQL:', postsIndexSqls);

  // 执行SQL
  executor.execute(postsSql);
  for (const indexSql of postsIndexSqls) {
    executor.execute(indexSql);
  }
}

// 测试插入数据
function testInsertData() {
  console.log('\n=== 测试插入数据 ===');

  // 插入用户
  const result = executor.execute(
    'INSERT INTO users (name, email, age) VALUES (@name, @email, @age)',
    { name: '张三', email: 'zhang@example.com', age: 30 }
  );

  console.log('插入结果:', result);

  // 查询数据验证时间戳
  const user = executor.queryOne('SELECT * FROM users WHERE id = 1');
  console.log('用户数据:', user);

  // 获取当前时间的毫秒级时间戳
  const now = Math.floor(Date.now());

  // 插入帖子（需要手动提供时间戳）
  const postResult = executor.execute(
    'INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES (@title, @content, @userId, @createdAt, @updatedAt)',
    {
      title: '测试帖子',
      content: '这是一个测试帖子内容',
      userId: 1,
      createdAt: now,
      updatedAt: now
    }
  );

  console.log('插入结果:', postResult);

  // 查询数据验证时间戳
  const post = executor.queryOne('SELECT * FROM posts WHERE id = 1');
  console.log('帖子数据:', post);
}

// 测试软删除
function testSoftDelete() {
  console.log('\n=== 测试软删除 ===');

  // 软删除用户
  executor.execute(
    'UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1'
  );

  // 查询验证软删除
  const user = executor.queryOne('SELECT * FROM users WHERE id = 1');
  console.log('已软删除的用户:', user);

  // 查询未删除的用户
  const activeUsers = executor.query('SELECT * FROM users WHERE deleted_at IS NULL');
  console.log('活跃用户数:', activeUsers.length);

  // 获取当前时间的毫秒级时间戳
  const now = Math.floor(Date.now());

  // 软删除帖子（毫秒格式）
  executor.execute(
    'UPDATE posts SET deleted_at = @now WHERE id = 1',
    { now }
  );

  // 查询验证软删除
  const post = executor.queryOne('SELECT * FROM posts WHERE id = 1');
  console.log('已软删除的帖子:', post);
}

// 运行测试
testCreateTable();
testInsertData();
testSoftDelete();



interface User {
  id: number;
  username: string;
  email: string;
  age: number;
  isActive: boolean;
}

// // 创建SQL构建器
// const userSql = SqlBuilder.forEntity<User>('users');
//
// // 构建简单查询
// const query = userSql
//   .select()
//   .where('age', '>', 18)
//   .orderBy('username')
//   .limit(10)
//   .build();

interface Post {
  id: number;
  userId: number;
  title: string;
  content: string;
  createdAt: Date;
}

// 关联查询用户及其文章
const userSql = SqlBuilder.forEntity<User>('users', 'u');
const postSql = SqlBuilder.forEntity<Post>('posts', 'p');

const joinQuery = userSql
  .select()
  .select(['u.id', 'u.username', 'p.id as postId', 'p.title', 'p.createdAt'])
  .leftJoin<Post>('posts', 'p', builder =>
    builder.eq('p.userId', 'u.id')
  )
  .where('u.isActive', '=', true)
  .orderBy('p.createdAt', 'DESC')
  .build();
